package com.jian.exceldaoru.controller;

import org.apache.commons.codec.binary.StringUtils;
import org.apache.poi.ss.usermodel.*;
import org.springframework.util.CollectionUtils;

import java.io.InputStream;
import java.util.ArrayList;
import java.util.Collections;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;

/**
 * Created by lijian on 2020/10/19 10:16
 */
public class ExcelUtils {

    public static ExcelBean excelToData(InputStream inputStream) {
        ExcelBean excelBean = new ExcelBean();
        List<String> detailList = new ArrayList<>();
        List<List<String>> allDetailList = new ArrayList<>();
        Integer startRow = 0;
        Integer endRow = 0;
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
            inputStream.close();
            //工作表对象
            Sheet sheet = workbook.getSheetAt(0);
            //总行数
            int rowLength = sheet.getLastRowNum();
            //工作表的列
            Row row = sheet.getRow(0);
            //总列数
            int colLength = row.getLastCellNum();
            //得到指定的单元格
            Cell cell = row.getCell(0);
            excelBean = getTableInfo(sheet,rowLength,row,colLength,cell);
            detailList = getDetailList(sheet,row,colLength,cell,excelBean);
            // 去除空的行
            detailList = detailList
                    .stream()
                    .filter(detail-> !CollectionUtils.isEmpty(StringUtil.splitStringBy(detail,",")))
                    .collect(Collectors.toList());
            for (int i=1;i< detailList.size();i++) {
                List<String> list =  new ArrayList<>();
                String s = detailList.get(i);
                if(s.length()>0){
                    s = s.substring(0,s.length()-1);
                    list = StringUtil.splitString(s,",");
                    if(!CollectionUtils.isEmpty(list)&&list.get(0).isEmpty()&&!CollectionUtils.isEmpty(allDetailList)){
                        list.set(0,allDetailList.get(i-1-1).get(0));
                    }
                }
                allDetailList.add(list);
            }
            excelBean.setDataList(allDetailList);
            System.out.println(excelBean);
        }catch (Exception e) {
            e.printStackTrace();
        }
        return excelBean;
    }

    private static ExcelBean getTableInfo(Sheet sheet,int rowLength,Row row,int colLength,Cell cell){
        ExcelBean excelBean = new ExcelBean();

        for (int i = 1; i <= rowLength; i++) {
            row = sheet.getRow(i);
            if(row!=null) {
                for (int j = 0; j < colLength; j++) {
                    cell = row.getCell(j);
                    //                    System.out.print(cell + ",");
                    if (cell != null) {
                        cell.setCellType(CellType.STRING);
                        String data = cell.getStringCellValue();
                        data = data.trim();
                        if (!data.isEmpty()) {
                            // 确定表头表尾的数据
                            if(data.indexOf(":")!=-1||data.indexOf("：")!=-1){
                                List<String> cellDatas = StringUtil.subStringBy(data);
                                switch (TableEnum.toType(cellDatas.get(0))) {
                                    case STATISTICAL_TIME:
                                        excelBean.setStatisticalTime(cellDatas.get(1));
                                        break;
                                    case STATISTICAL_TYPE:
                                        excelBean.setStatisticalType(cellDatas.get(1));
                                        break;
                                    case LINE_NAME:
                                        excelBean.setLineName(cellDatas.get(1));
                                        break;
                                    case TABULATION_TIME:
                                        excelBean.setTabulationTime(cellDatas.get(1));
                                        break;
                                    case TABULATOR:
                                        excelBean.setTabulator(cellDatas.get(1));
                                        excelBean.setStartRow(i);
                                        break;
                                    case TABULATING_UNIT:
                                        excelBean.setTabulatingUnit(cellDatas.get(1));
                                        excelBean.setEndRow(i);
                                        break;
                                    default:
                                        break;
                                }
                            }
                        }
                    }
                }
            }
        }
        return excelBean;
    }
    private static List<String> getDetailList(Sheet sheet,Row row,int colLength,Cell cell,ExcelBean excelBean){
        List<String> detailList = new ArrayList<>();
        for (int i = excelBean.getStartRow()+1; i <excelBean.getEndRow(); i++) {
            row = sheet.getRow(i);
            if (row != null) {
                StringBuilder sb = new StringBuilder();
                for (int j = 0; j < colLength; j++) {
                    cell = row.getCell(j);
                    if (cell != null) {
                        cell.setCellType(CellType.STRING);
                        String data = cell.getStringCellValue();
                        data = data.trim();
                        sb.append(data).append(",");
                    }
                }
                detailList.add(sb.toString());
            }
        }
        return detailList;
    }
//    public static List<ExcelBean> excelToShopIdList(InputStream inputStream,Integer listStartRow) {
//        List<ExcelBean> list = new ArrayList<>();
//        Workbook workbook = null;
//        try {
//            workbook = WorkbookFactory.create(inputStream);
//            inputStream.close();
//            //工作表对象
//            Sheet sheet = workbook.getSheetAt(0);
//            //总行数
//            int rowLength = sheet.getLastRowNum();
//            //            System.out.println("总行数有多少行" + rowLength);
//            //工作表的列
//            Row row = sheet.getRow(0);
//            //总列数
//            int colLength = row.getLastCellNum();
//            //            System.out.println("总列数有多少列" + colLength);
//            //得到指定的单元格
//            Cell cell = row.getCell(0);
//            List<String> listStr = new ArrayList<>();
//            ExcelBean excelBean = new ExcelBean();
//            for (int i = 1; i <= rowLength; i++) {
//                row = sheet.getRow(i);
//                if(row!=null) {
//                    for (int j = 0; j < colLength; j++) {
//                        cell = row.getCell(j);
//                        //                    System.out.print(cell + ",");
//                        if (cell != null) {
//                            cell.setCellType(CellType.STRING);
//                            String data = cell.getStringCellValue();
//                            data = data.trim();
//                            if (!data.isEmpty()) {
//                                // 添加表元素
//                                List<String> cellDatas = StringUtil.subStringBy(data);
//                                switch (TableEnum.toType(cellDatas.get(0))) {
//                                    case STATISTICAL_TIME:
//                                        excelBean.setStatisticalTime(cellDatas.get(1));
//                                        break;
//                                    case LINE_NAME:
//                                        excelBean.setLineName(cellDatas.get(1));
//                                        break;
//                                    case TABULATION_TIME:
//                                        excelBean.setTabulationTime(cellDatas.get(1));
//                                        break;
//                                    case TABULATOR:
//                                        excelBean.setTabulator(cellDatas.get(1));
//                                        break;
//                                    case TABULATING_UNIT:
//                                        excelBean.setTabulatingUnit(cellDatas.get(1));
//                                        break;
//                                    default:
//                                        break;
//                                }
//                            }
//                        }
//                    }
//                }
//            }
//            System.out.println(excelBean);
//        } catch (Exception e) {
//            e.printStackTrace();
//        }
//        return list;
//    }
}
